{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c600bccd",
   "metadata": {},
   "source": [
    "### pandas的分组和聚合操作\n",
    "1. DataFrame.GroupBy()  分组-->聚合\n",
    "2. DataFrame.pivot_table() 数据透视表\n",
    "3. DataFrame.crosstab() 交叉表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2b59bf41",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7e5f310b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B</td>\n",
       "      <td>男</td>\n",
       "      <td>18</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>女</td>\n",
       "      <td>19</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B</td>\n",
       "      <td>男</td>\n",
       "      <td>17</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>女</td>\n",
       "      <td>19</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>男</td>\n",
       "      <td>16</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>女</td>\n",
       "      <td>14</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B</td>\n",
       "      <td>男</td>\n",
       "      <td>15</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A</td>\n",
       "      <td>女</td>\n",
       "      <td>17</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>男</td>\n",
       "      <td>17</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  班级 性别  年龄  成绩\n",
       "0  B  男  18  91\n",
       "1  A  女  19  81\n",
       "2  B  男  17  71\n",
       "3  B  女  19  61\n",
       "4  A  男  16  51\n",
       "5  A  女  14  71\n",
       "6  B  男  15  86\n",
       "7  A  女  17  98\n",
       "8  B  男  17  77"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(\n",
    "   [['B', '男', 18, 91],\n",
    "    ['A', '女', 19, 81],\n",
    "    ['B', '男', 17, 71],\n",
    "    ['B', '女', 19, 61],\n",
    "    ['A', '男', 16, 51],\n",
    "    ['A', '女', 14, 71],\n",
    "    ['B', '男', 15, 86],\n",
    "    ['A', '女', 17, 98],\n",
    "    ['B', '男', 17, 77]],\n",
    "    columns = ['班级', '性别', '年龄', '成绩']\n",
    "         )\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "94607524",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>年龄</th>\n",
       "      <th>性别</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>16.5</td>\n",
       "      <td>4</td>\n",
       "      <td>301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>17.2</td>\n",
       "      <td>5</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      年龄  性别   成绩\n",
       "班级               \n",
       "A   16.5   4  301\n",
       "B   17.2   5  386"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groupby 先分组, 分组后的对象.agg 实现聚合，返回结果的DataFrame\n",
    "df.groupby('班级').agg({\"年龄\": np.mean, \n",
    "                      \"性别\":len,\n",
    "                      \"成绩\":np.sum\n",
    "                     })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d76a3f91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "性别\n",
       "女    77.75\n",
       "男    75.20\n",
       "Name: 成绩, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 用性别分组，求男生和女生的平均分\n",
    "df.groupby('性别')['成绩'].mean()  # 返回 Series\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fedb1beb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "性别\n",
       "女    77.75\n",
       "男    75.20\n",
       "Name: 成绩, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 等同于\n",
    "df.groupby('性别').agg({\"成绩\":np.mean})[\"成绩\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c209a7f",
   "metadata": {},
   "source": [
    "### 数据透视表 pivot_table\n",
    "```\n",
    "DataFrame.privot_table(index=['xxx'], columns['yyy'], \n",
    "aggfunc='mean')\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0f765e5f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>年龄</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>性别</th>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">女</th>\n",
       "      <th>A</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>83.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>19.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">男</th>\n",
       "      <th>A</th>\n",
       "      <td>16.000000</td>\n",
       "      <td>51.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>16.750000</td>\n",
       "      <td>81.250000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              年龄         成绩\n",
       "性别 班级                      \n",
       "女  A   16.666667  83.333333\n",
       "   B   19.000000  61.000000\n",
       "男  A   16.000000  51.000000\n",
       "   B   16.750000  81.250000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=['性别', '班级'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "060029cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>班级</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>年龄</th>\n",
       "      <td>16.50</td>\n",
       "      <td>17.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>成绩</th>\n",
       "      <td>75.25</td>\n",
       "      <td>77.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "班级      A     B\n",
       "年龄  16.50  17.2\n",
       "成绩  75.25  77.2"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(columns='班级')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "408d0cd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">年龄</th>\n",
       "      <th colspan=\"2\" halign=\"left\">成绩</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>性别</th>\n",
       "      <th>女</th>\n",
       "      <th>男</th>\n",
       "      <th>女</th>\n",
       "      <th>男</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.00</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>51.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>19.000000</td>\n",
       "      <td>16.75</td>\n",
       "      <td>61.000000</td>\n",
       "      <td>81.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           年龄                成绩       \n",
       "性别          女      男          女      男\n",
       "班级                                    \n",
       "A   16.666667  16.00  83.333333  51.00\n",
       "B   19.000000  16.75  61.000000  81.25"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='班级', columns='性别')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "537d5a2d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>年龄</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>66</td>\n",
       "      <td>301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>86</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    年龄   成绩\n",
       "班级         \n",
       "A   66  301\n",
       "B   86  386"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='班级', aggfunc=np.sum) # 班级分组再聚合"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e087cfd5",
   "metadata": {},
   "source": [
    "### 交叉表\n",
    "pandas.crosstab(index=系列数据, columns=系列数据)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "33cee8eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>性别</th>\n",
       "      <th>女</th>\n",
       "      <th>男</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "性别  女  男\n",
       "班级      \n",
       "A   3  1\n",
       "B   1  4"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 求A,B 两个班级的, 不同性别的学生个数\n",
    "pd.crosstab(df[\"班级\"],df[\"性别\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f988bb92",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
